{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f8e74fc4",
   "metadata": {},
   "source": [
    "# data import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1e97b1c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>489434</td>\n",
       "      <td>85048</td>\n",
       "      <td>15CM CHRISTMAS GLASS BALL 20 LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.95</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323P</td>\n",
       "      <td>PINK CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323W</td>\n",
       "      <td>WHITE CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>489434</td>\n",
       "      <td>22041</td>\n",
       "      <td>RECORD FRAME 7\" SINGLE SIZE</td>\n",
       "      <td>48</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>2.10</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>489434</td>\n",
       "      <td>21232</td>\n",
       "      <td>STRAWBERRY CERAMIC TRINKET BOX</td>\n",
       "      <td>24</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>1.25</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Invoice StockCode                          Description  Quantity  \\\n",
       "0  489434     85048  15CM CHRISTMAS GLASS BALL 20 LIGHTS        12   \n",
       "1  489434    79323P                   PINK CHERRY LIGHTS        12   \n",
       "2  489434    79323W                  WHITE CHERRY LIGHTS        12   \n",
       "3  489434     22041         RECORD FRAME 7\" SINGLE SIZE         48   \n",
       "4  489434     21232       STRAWBERRY CERAMIC TRINKET BOX        24   \n",
       "\n",
       "           InvoiceDate  Price  Customer ID         Country        Date  \n",
       "0  2009-12-01 07:45:00   6.95      13085.0  United Kingdom  2009-12-01  \n",
       "1  2009-12-01 07:45:00   6.75      13085.0  United Kingdom  2009-12-01  \n",
       "2  2009-12-01 07:45:00   6.75      13085.0  United Kingdom  2009-12-01  \n",
       "3  2009-12-01 07:45:00   2.10      13085.0  United Kingdom  2009-12-01  \n",
       "4  2009-12-01 07:45:00   1.25      13085.0  United Kingdom  2009-12-01  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib as mpl\n",
    "from matplotlib import pyplot as plt\n",
    "import warnings\n",
    "import os\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data_path = \"Data_L1.csv\"\n",
    "data_df = pd.read_csv(data_path,\n",
    "            encoding='utf-8')\n",
    "data_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "67ec38f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     806702\n",
       "unique         2\n",
       "top        False\n",
       "freq      788315\n",
       "Name: cancel_invoice, dtype: object"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data_raw 用来保存所有cancel的订单信息\n",
    "data_raw = data_df.copy()\n",
    "data_raw[\"cancel_invoice\"] = data_raw[\"Invoice\"].str.startswith('C')\n",
    "data_raw = data_raw[data_df[\"Date\"]<'2011-12-01']\n",
    "data_raw.head(4)\n",
    "data_raw[\"cancel_invoice\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cb4ee6e",
   "metadata": {},
   "source": [
    "# data tweak"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee1f4e3f",
   "metadata": {},
   "source": [
    "1. 我们确定标签的截止日期为2011-12-01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "de1bede3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "min:2009-12-01 00:00:00\n",
      "max:2011-11-30 00:00:00\n",
      "788315\n"
     ]
    }
   ],
   "source": [
    "data_df[\"cancel_invoice\"] = data_df[\"Invoice\"].str.startswith('C')\n",
    "data_df = data_df[(data_df[\"cancel_invoice\"]==False)&(data_df[\"Date\"]<'2011-12-01')]\n",
    "data_df[\"Date\"] = pd.to_datetime(data_df[\"Date\"])\n",
    "print(\"min:{}\\nmax:{}\".format(min(data_df[\"Date\"]),max(data_df[\"Date\"])))\n",
    "print(len(data_df))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61eeec67",
   "metadata": {},
   "source": [
    "2. 确定cohort group和cohort index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a1e1b196",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df['GMV'] = data_df[\"Price\"] * data_df[\"Quantity\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "20ab768d",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df[\"first_purchase\"] = pd.to_datetime(data_df.groupby(\"Customer ID\")[\"Date\"] \\\n",
    "                                               .transform(\"min\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dc0278e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df[\"cohort_index\"] = (data_df[\"Date\"].dt.year - data_df[\"first_purchase\"].dt.year) *12 \\\n",
    "                          + (data_df[\"Date\"].dt.month - data_df[\"first_purchase\"].dt.month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4bed0e3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df[\"main_country\"] = data_df.sort_values([\"Customer ID\",\"Date\",\"Invoice\"]) \\\n",
    "                                 .groupby(\"Customer ID\")[\"Country\"].transform(\"first\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a2849865",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df[\"country_group\"] = data_df[\"main_country\"] \\\n",
    "                            .apply(lambda row: 'UK' if row==\"United Kingdom\" else \"Others\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4cfc8bf1",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df[\"cohort_month\"] = data_df[\"first_purchase\"].dt.year * 100 \\\n",
    "                                + data_df[\"first_purchase\"].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6059d0ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df[\"invoice_year\"] = data_df[\"Date\"].dt.year\n",
    "data_df[\"invoice_month\"] = data_df[\"Date\"].dt.month\n",
    "data_df[\"invoice_ym\"] = data_df[\"invoice_year\"] * 100 + data_df[\"invoice_month\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "96c7c855",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "      <th>Date</th>\n",
       "      <th>cancel_invoice</th>\n",
       "      <th>GMV</th>\n",
       "      <th>first_purchase</th>\n",
       "      <th>cohort_index</th>\n",
       "      <th>main_country</th>\n",
       "      <th>country_group</th>\n",
       "      <th>cohort_month</th>\n",
       "      <th>invoice_year</th>\n",
       "      <th>invoice_month</th>\n",
       "      <th>invoice_ym</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>489434</td>\n",
       "      <td>85048</td>\n",
       "      <td>15CM CHRISTMAS GLASS BALL 20 LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.95</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>False</td>\n",
       "      <td>83.4</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>UK</td>\n",
       "      <td>200912</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>200912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323P</td>\n",
       "      <td>PINK CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>False</td>\n",
       "      <td>81.0</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>UK</td>\n",
       "      <td>200912</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>200912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323W</td>\n",
       "      <td>WHITE CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>False</td>\n",
       "      <td>81.0</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>UK</td>\n",
       "      <td>200912</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>200912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>489434</td>\n",
       "      <td>22041</td>\n",
       "      <td>RECORD FRAME 7\" SINGLE SIZE</td>\n",
       "      <td>48</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>2.10</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>False</td>\n",
       "      <td>100.8</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>UK</td>\n",
       "      <td>200912</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>200912</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Invoice StockCode                          Description  Quantity  \\\n",
       "0  489434     85048  15CM CHRISTMAS GLASS BALL 20 LIGHTS        12   \n",
       "1  489434    79323P                   PINK CHERRY LIGHTS        12   \n",
       "2  489434    79323W                  WHITE CHERRY LIGHTS        12   \n",
       "3  489434     22041         RECORD FRAME 7\" SINGLE SIZE         48   \n",
       "\n",
       "           InvoiceDate  Price  Customer ID         Country       Date  \\\n",
       "0  2009-12-01 07:45:00   6.95      13085.0  United Kingdom 2009-12-01   \n",
       "1  2009-12-01 07:45:00   6.75      13085.0  United Kingdom 2009-12-01   \n",
       "2  2009-12-01 07:45:00   6.75      13085.0  United Kingdom 2009-12-01   \n",
       "3  2009-12-01 07:45:00   2.10      13085.0  United Kingdom 2009-12-01   \n",
       "\n",
       "   cancel_invoice    GMV first_purchase  cohort_index    main_country  \\\n",
       "0           False   83.4     2009-12-01             0  United Kingdom   \n",
       "1           False   81.0     2009-12-01             0  United Kingdom   \n",
       "2           False   81.0     2009-12-01             0  United Kingdom   \n",
       "3           False  100.8     2009-12-01             0  United Kingdom   \n",
       "\n",
       "  country_group  cohort_month  invoice_year  invoice_month  invoice_ym  \n",
       "0            UK        200912          2009             12      200912  \n",
       "1            UK        200912          2009             12      200912  \n",
       "2            UK        200912          2009             12      200912  \n",
       "3            UK        200912          2009             12      200912  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.head(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cb475923",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_c = data_df.sort_values([\"Customer ID\",\"cohort_index\",\"Invoice\"]) \\\n",
    "    .groupby([\"Customer ID\",\"cohort_month\",\"cohort_index\",\"invoice_year\",\"invoice_month\"]) \\\n",
    "    .agg({'Invoice':'nunique',\"GMV\":\"sum\"}) \\\n",
    "    .reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3947c241",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_c[\"cohort_lead\"] = data_c.groupby([\"Customer ID\"])[\"cohort_index\"].shift(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "96e5985f",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_c[\"trans_gap\"] = data_c[\"cohort_index\"] - data_c[\"cohort_lead\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f4de042e",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_c['last_gap'] = (2011 - data_c[\"invoice_year\"])*12 \\\n",
    "                        + (12 - data_c[\"invoice_month\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "331baea1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>cohort_month</th>\n",
       "      <th>cohort_index</th>\n",
       "      <th>invoice_year</th>\n",
       "      <th>invoice_month</th>\n",
       "      <th>Invoice</th>\n",
       "      <th>GMV</th>\n",
       "      <th>cohort_lead</th>\n",
       "      <th>trans_gap</th>\n",
       "      <th>last_gap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12346.0</td>\n",
       "      <td>200912</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>113.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12346.0</td>\n",
       "      <td>200912</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>90.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12346.0</td>\n",
       "      <td>200912</td>\n",
       "      <td>3</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>27.05</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12346.0</td>\n",
       "      <td>200912</td>\n",
       "      <td>6</td>\n",
       "      <td>2010</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>142.31</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer ID  cohort_month  cohort_index  invoice_year  invoice_month  \\\n",
       "0      12346.0        200912             0          2009             12   \n",
       "1      12346.0        200912             1          2010              1   \n",
       "2      12346.0        200912             3          2010              3   \n",
       "3      12346.0        200912             6          2010              6   \n",
       "\n",
       "   Invoice     GMV  cohort_lead  trans_gap  last_gap  \n",
       "0        5  113.50          NaN        NaN        24  \n",
       "1        4   90.00          0.0        1.0        23  \n",
       "2        1   27.05          1.0        2.0        21  \n",
       "3        1  142.31          3.0        3.0        18  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_c.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00a90678",
   "metadata": {},
   "source": [
    "# feature suggestion\n",
    "<font color=red>* 注；以下观察周期都是指用户有交易的月份，而非全部月份</font>\n",
    "1. Recency - 上一次消费距观察日(2011-12-01)时间\n",
    "2. Frequency - 观察周期内交易数量\n",
    "3. Monetary - 观察周期内产生GMV\n",
    "4. Monthly_F - 顾客有交易月度Frequency均值\n",
    "5. Monthly_M - 顾客有交易月度Monetary均值\n",
    "6. Monthly_F_2 - 顾客全月度Frequency均值\n",
    "7. Duration - 顾客两次交易时间<font color=red>最长间隔</font>\n",
    "8. Gap_min, Gap_max, Gap_mean, Gap_median - 顾客交易间隔的分布集中统计学变量\n",
    "9. Total_invoices - 观察周期内顾客交易总量\n",
    "10. Cancel_invoices - 观察周期内顾客取消订单总量\n",
    "11. Cancel_rate - 观察周期内取消订单占总订单量比率\n",
    "12. Product_wide - 观察周期内顾客购买商品种类数\n",
    "13. Price_mean - 观察周期内顾客购买商品平均价格\n",
    "14. Quantity_sum - 顾客购买商品总数量\n",
    "15. APV(Avg. GMV of product purchased) - 顾客购买件单价\n",
    "16. ATV(Avg. GMV of transaction) - 顾客产生每单交易平均GMV\n",
    "17. Country_group - 顾客所处国家，one-hot数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "170b0551",
   "metadata": {},
   "source": [
    "# feature generation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c20f60c6",
   "metadata": {},
   "source": [
    "## Duration Monthly_M Monthly_F Gap_4 Recency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "120b934b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12346.0</th>\n",
       "      <td>13</td>\n",
       "      <td>2.4</td>\n",
       "      <td>15511.292000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.250000</td>\n",
       "      <td>2.5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12347.0</th>\n",
       "      <td>12</td>\n",
       "      <td>1.0</td>\n",
       "      <td>772.642857</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12348.0</th>\n",
       "      <td>12</td>\n",
       "      <td>1.0</td>\n",
       "      <td>403.880000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12349.0</th>\n",
       "      <td>19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1107.172500</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>6.333333</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             duration  monthly_f     monthly_m  gap_min  gap_max  gap_mean  \\\n",
       "Customer ID                                                                  \n",
       "12346.0            13        2.4  15511.292000      1.0      7.0  3.250000   \n",
       "12347.0            12        1.0    772.642857      1.0      3.0  2.000000   \n",
       "12348.0            12        1.0    403.880000      1.0      5.0  3.000000   \n",
       "12349.0            19        1.0   1107.172500      1.0     13.0  6.333333   \n",
       "\n",
       "             gap_median  recency  \n",
       "Customer ID                       \n",
       "12346.0             2.5       11  \n",
       "12347.0             2.0        2  \n",
       "12348.0             3.0        3  \n",
       "12349.0             5.0        1  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Duration Monthly_M Monthly_F Gap_4 Recency\n",
    "customer_gap = data_c.groupby(\"Customer ID\") \\\n",
    "                     .agg({'cohort_index':'max',\n",
    "                           'Invoice':'mean',\n",
    "                           'GMV':'mean',\n",
    "                           'trans_gap':['min','max','mean','median'],\n",
    "                           'last_gap':'min'})\n",
    "customer_gap.columns=[\"duration\",\"monthly_f\",\"monthly_m\",\n",
    "                      \"gap_min\",\"gap_max\",\"gap_mean\",\n",
    "                      \"gap_median\",\"recency\"]\n",
    "customer_gap.head(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "05c0b707",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>cancel_invoice</th>\n",
       "      <th>total_invoice</th>\n",
       "      <th>cancel_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12346.0</th>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>17</td>\n",
       "      <td>0.294118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12347.0</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12348.0</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12349.0</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0.200000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             closed_invoice  cancel_invoice  total_invoice  cancel_rate\n",
       "Customer ID                                                            \n",
       "12346.0                  12               5             17     0.294118\n",
       "12347.0                   7               0              7     0.000000\n",
       "12348.0                   5               0              5     0.000000\n",
       "12349.0                   4               1              5     0.200000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total_invoices Cancel_invoice Cancel_rate\n",
    "customer_cancel = pd.pivot_table(data=data_raw, \n",
    "                                 index=['Customer ID'],\n",
    "                                 values='Invoice',\n",
    "                                 columns='cancel_invoice',\n",
    "                                 aggfunc=pd.Series.nunique,\n",
    "                                 fill_value=0)\n",
    "customer_cancel.columns=['closed_invoice','cancel_invoice']\n",
    "customer_cancel[\"total_invoice\"] = customer_cancel['closed_invoice'] \\\n",
    "                                    + customer_cancel[\"cancel_invoice\"]\n",
    "customer_cancel['cancel_rate'] = customer_cancel['cancel_invoice'] \\\n",
    "                                 / customer_cancel['total_invoice']\n",
    "customer_cancel.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a47440b4",
   "metadata": {},
   "source": [
    "## Frequency APV ATV Monetary Product_Wide Price_mean Quantity_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8346431c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12346.0</th>\n",
       "      <td>12</td>\n",
       "      <td>2281.072353</td>\n",
       "      <td>77556.46</td>\n",
       "      <td>27</td>\n",
       "      <td>6.100000</td>\n",
       "      <td>74285</td>\n",
       "      <td>6463.038333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12347.0</th>\n",
       "      <td>7</td>\n",
       "      <td>22.349174</td>\n",
       "      <td>5408.50</td>\n",
       "      <td>123</td>\n",
       "      <td>2.605868</td>\n",
       "      <td>3094</td>\n",
       "      <td>772.642857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12348.0</th>\n",
       "      <td>5</td>\n",
       "      <td>39.596078</td>\n",
       "      <td>2019.40</td>\n",
       "      <td>25</td>\n",
       "      <td>3.786275</td>\n",
       "      <td>2714</td>\n",
       "      <td>403.880000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12349.0</th>\n",
       "      <td>4</td>\n",
       "      <td>25.306800</td>\n",
       "      <td>4428.69</td>\n",
       "      <td>138</td>\n",
       "      <td>8.459657</td>\n",
       "      <td>1624</td>\n",
       "      <td>1107.172500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             frequency          APV  monetary  product_wide  price_mean  \\\n",
       "Customer ID                                                               \n",
       "12346.0             12  2281.072353  77556.46            27    6.100000   \n",
       "12347.0              7    22.349174   5408.50           123    2.605868   \n",
       "12348.0              5    39.596078   2019.40            25    3.786275   \n",
       "12349.0              4    25.306800   4428.69           138    8.459657   \n",
       "\n",
       "             quantity_sum          ATV  \n",
       "Customer ID                             \n",
       "12346.0             74285  6463.038333  \n",
       "12347.0              3094   772.642857  \n",
       "12348.0              2714   403.880000  \n",
       "12349.0              1624  1107.172500  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Frequency APV ATV Monetary Product_Wide Price_mean Quantity_sum\n",
    "customer = data_df.groupby([\"Customer ID\"]) \\\n",
    "                 .agg({\"Invoice\":\"nunique\",\n",
    "                       \"GMV\":[\"mean\",\"sum\"],\n",
    "                       \"StockCode\":\"nunique\",\n",
    "                       \"Price\":\"mean\",\n",
    "                       \"Quantity\":\"sum\"})\n",
    "customer.columns=[\"frequency\",\"APV\",\"monetary\",\"product_wide\",\n",
    "                  \"price_mean\",\"quantity_sum\"]\n",
    "customer[\"ATV\"] = customer[\"monetary\"]/customer[\"frequency\"]\n",
    "customer.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b06fdb58",
   "metadata": {},
   "source": [
    "## one-hot encoding on country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5e06f2c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13085.0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13078.0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15362.0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18102.0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             c_Others  c_UK\n",
       "Customer ID                \n",
       "13085.0             0     1\n",
       "13078.0             0     1\n",
       "15362.0             0     1\n",
       "18102.0             0     1"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# one-hot encoding on country\n",
    "customer_country = pd.get_dummies(data=data_df[[\"Customer ID\",\"country_group\"]].drop_duplicates(),\n",
    "                                  columns=[\"country_group\"],\n",
    "                                  prefix='c') \\\n",
    "                     .set_index('Customer ID')\n",
    "customer_country.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e6df531",
   "metadata": {},
   "source": [
    "将所有的featuers组合到一起"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "77373c2d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>total_invoice</th>\n",
       "      <th>cancel_rate</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12346.0</th>\n",
       "      <td>12</td>\n",
       "      <td>2281.072353</td>\n",
       "      <td>77556.46</td>\n",
       "      <td>27</td>\n",
       "      <td>6.100000</td>\n",
       "      <td>74285</td>\n",
       "      <td>6463.038333</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>0.294118</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>15511.292000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.250000</td>\n",
       "      <td>2.5</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12347.0</th>\n",
       "      <td>7</td>\n",
       "      <td>22.349174</td>\n",
       "      <td>5408.50</td>\n",
       "      <td>123</td>\n",
       "      <td>2.605868</td>\n",
       "      <td>3094</td>\n",
       "      <td>772.642857</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>772.642857</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12348.0</th>\n",
       "      <td>5</td>\n",
       "      <td>39.596078</td>\n",
       "      <td>2019.40</td>\n",
       "      <td>25</td>\n",
       "      <td>3.786275</td>\n",
       "      <td>2714</td>\n",
       "      <td>403.880000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>403.880000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12349.0</th>\n",
       "      <td>4</td>\n",
       "      <td>25.306800</td>\n",
       "      <td>4428.69</td>\n",
       "      <td>138</td>\n",
       "      <td>8.459657</td>\n",
       "      <td>1624</td>\n",
       "      <td>1107.172500</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1107.172500</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>6.333333</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             frequency          APV  monetary  product_wide  price_mean  \\\n",
       "Customer ID                                                               \n",
       "12346.0             12  2281.072353  77556.46            27    6.100000   \n",
       "12347.0              7    22.349174   5408.50           123    2.605868   \n",
       "12348.0              5    39.596078   2019.40            25    3.786275   \n",
       "12349.0              4    25.306800   4428.69           138    8.459657   \n",
       "\n",
       "             quantity_sum          ATV  c_Others  c_UK  closed_invoice  ...  \\\n",
       "Customer ID                                                             ...   \n",
       "12346.0             74285  6463.038333         0     1              12  ...   \n",
       "12347.0              3094   772.642857         1     0               7  ...   \n",
       "12348.0              2714   403.880000         1     0               5  ...   \n",
       "12349.0              1624  1107.172500         1     0               4  ...   \n",
       "\n",
       "             total_invoice  cancel_rate  duration  monthly_f     monthly_m  \\\n",
       "Customer ID                                                                  \n",
       "12346.0                 17     0.294118      13.0        2.4  15511.292000   \n",
       "12347.0                  7     0.000000      12.0        1.0    772.642857   \n",
       "12348.0                  5     0.000000      12.0        1.0    403.880000   \n",
       "12349.0                  5     0.200000      19.0        1.0   1107.172500   \n",
       "\n",
       "             gap_min  gap_max  gap_mean  gap_median  recency  \n",
       "Customer ID                                                   \n",
       "12346.0          1.0      7.0  3.250000         2.5     11.0  \n",
       "12347.0          1.0      3.0  2.000000         2.0      2.0  \n",
       "12348.0          1.0      5.0  3.000000         3.0      3.0  \n",
       "12349.0          1.0     13.0  6.333333         5.0      1.0  \n",
       "\n",
       "[4 rows x 21 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_list = customer.join(customer_country.join(customer_cancel.join(customer_gap)))\n",
    "feature_list.head(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "991a8b92",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add Monthly_F_2 into feature_list\n",
    "feature_list[\"monthly_f_2\"] = feature_list[\"frequency\"]/(feature_list[\"duration\"]+1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7611e9d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "      <th>churn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12346.0</th>\n",
       "      <td>12</td>\n",
       "      <td>2281.072353</td>\n",
       "      <td>77556.46</td>\n",
       "      <td>27</td>\n",
       "      <td>6.100000</td>\n",
       "      <td>74285</td>\n",
       "      <td>6463.038333</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>15511.292000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.250000</td>\n",
       "      <td>2.5</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12347.0</th>\n",
       "      <td>7</td>\n",
       "      <td>22.349174</td>\n",
       "      <td>5408.50</td>\n",
       "      <td>123</td>\n",
       "      <td>2.605868</td>\n",
       "      <td>3094</td>\n",
       "      <td>772.642857</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>772.642857</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.538462</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12348.0</th>\n",
       "      <td>5</td>\n",
       "      <td>39.596078</td>\n",
       "      <td>2019.40</td>\n",
       "      <td>25</td>\n",
       "      <td>3.786275</td>\n",
       "      <td>2714</td>\n",
       "      <td>403.880000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>403.880000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.384615</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12349.0</th>\n",
       "      <td>4</td>\n",
       "      <td>25.306800</td>\n",
       "      <td>4428.69</td>\n",
       "      <td>138</td>\n",
       "      <td>8.459657</td>\n",
       "      <td>1624</td>\n",
       "      <td>1107.172500</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1107.172500</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>6.333333</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             frequency          APV  monetary  product_wide  price_mean  \\\n",
       "Customer ID                                                               \n",
       "12346.0             12  2281.072353  77556.46            27    6.100000   \n",
       "12347.0              7    22.349174   5408.50           123    2.605868   \n",
       "12348.0              5    39.596078   2019.40            25    3.786275   \n",
       "12349.0              4    25.306800   4428.69           138    8.459657   \n",
       "\n",
       "             quantity_sum          ATV  c_Others  c_UK  closed_invoice  ...  \\\n",
       "Customer ID                                                             ...   \n",
       "12346.0             74285  6463.038333         0     1              12  ...   \n",
       "12347.0              3094   772.642857         1     0               7  ...   \n",
       "12348.0              2714   403.880000         1     0               5  ...   \n",
       "12349.0              1624  1107.172500         1     0               4  ...   \n",
       "\n",
       "             duration  monthly_f     monthly_m  gap_min  gap_max  gap_mean  \\\n",
       "Customer ID                                                                  \n",
       "12346.0          13.0        2.4  15511.292000      1.0      7.0  3.250000   \n",
       "12347.0          12.0        1.0    772.642857      1.0      3.0  2.000000   \n",
       "12348.0          12.0        1.0    403.880000      1.0      5.0  3.000000   \n",
       "12349.0          19.0        1.0   1107.172500      1.0     13.0  6.333333   \n",
       "\n",
       "             gap_median  recency  monthly_f_2  churn  \n",
       "Customer ID                                           \n",
       "12346.0             2.5     11.0     0.857143      1  \n",
       "12347.0             2.0      2.0     0.538462      0  \n",
       "12348.0             3.0      3.0     0.384615      0  \n",
       "12349.0             5.0      1.0     0.200000      0  \n",
       "\n",
       "[4 rows x 23 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# generate tag for churn\n",
    "feature_list['churn'] = feature_list[\"recency\"].apply(lambda row: 1 if row>5 else 0)\n",
    "feature_list.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9186ad1a",
   "metadata": {},
   "source": [
    "# feature overview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "59b12247",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "      <th>churn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>4036.000000</td>\n",
       "      <td>4036.000000</td>\n",
       "      <td>4036.000000</td>\n",
       "      <td>4036.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "      <td>5853.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>6.184350</td>\n",
       "      <td>37.897441</td>\n",
       "      <td>2942.972559</td>\n",
       "      <td>81.012301</td>\n",
       "      <td>8.606924</td>\n",
       "      <td>1782.591321</td>\n",
       "      <td>377.123733</td>\n",
       "      <td>0.089185</td>\n",
       "      <td>0.910815</td>\n",
       "      <td>6.184350</td>\n",
       "      <td>...</td>\n",
       "      <td>8.760465</td>\n",
       "      <td>1.229117</td>\n",
       "      <td>495.254898</td>\n",
       "      <td>2.822349</td>\n",
       "      <td>5.960605</td>\n",
       "      <td>4.044379</td>\n",
       "      <td>3.760530</td>\n",
       "      <td>7.081668</td>\n",
       "      <td>0.789845</td>\n",
       "      <td>0.439433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>12.784213</td>\n",
       "      <td>265.324075</td>\n",
       "      <td>14338.715259</td>\n",
       "      <td>115.114052</td>\n",
       "      <td>176.692016</td>\n",
       "      <td>8790.919323</td>\n",
       "      <td>526.012549</td>\n",
       "      <td>0.285035</td>\n",
       "      <td>0.285035</td>\n",
       "      <td>12.784213</td>\n",
       "      <td>...</td>\n",
       "      <td>8.343552</td>\n",
       "      <td>0.613260</td>\n",
       "      <td>1093.116996</td>\n",
       "      <td>3.484632</td>\n",
       "      <td>3.903696</td>\n",
       "      <td>3.369243</td>\n",
       "      <td>3.483002</td>\n",
       "      <td>6.756999</td>\n",
       "      <td>0.679437</td>\n",
       "      <td>0.496360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.083333</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>11.469000</td>\n",
       "      <td>343.450000</td>\n",
       "      <td>19.000000</td>\n",
       "      <td>2.294643</td>\n",
       "      <td>187.000000</td>\n",
       "      <td>181.495000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>201.396000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.818182</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>17.316316</td>\n",
       "      <td>875.970000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>2.971423</td>\n",
       "      <td>485.000000</td>\n",
       "      <td>285.334286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>324.800000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>24.085714</td>\n",
       "      <td>2263.300000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>3.861033</td>\n",
       "      <td>1353.000000</td>\n",
       "      <td>422.700000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>1.285714</td>\n",
       "      <td>508.800000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>389.000000</td>\n",
       "      <td>13305.500000</td>\n",
       "      <td>597336.110000</td>\n",
       "      <td>2515.000000</td>\n",
       "      <td>10953.500000</td>\n",
       "      <td>359875.000000</td>\n",
       "      <td>14844.766667</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>389.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>19.375000</td>\n",
       "      <td>39916.500000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>16.208333</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         frequency           APV       monetary  product_wide    price_mean  \\\n",
       "count  5853.000000   5853.000000    5853.000000   5853.000000   5853.000000   \n",
       "mean      6.184350     37.897441    2942.972559     81.012301      8.606924   \n",
       "std      12.784213    265.324075   14338.715259    115.114052    176.692016   \n",
       "min       1.000000      0.000000       0.000000      1.000000      0.000000   \n",
       "25%       1.000000     11.469000     343.450000     19.000000      2.294643   \n",
       "50%       3.000000     17.316316     875.970000     44.000000      2.971423   \n",
       "75%       7.000000     24.085714    2263.300000    101.000000      3.861033   \n",
       "max     389.000000  13305.500000  597336.110000   2515.000000  10953.500000   \n",
       "\n",
       "        quantity_sum           ATV     c_Others         c_UK  closed_invoice  \\\n",
       "count    5853.000000   5853.000000  5853.000000  5853.000000     5853.000000   \n",
       "mean     1782.591321    377.123733     0.089185     0.910815        6.184350   \n",
       "std      8790.919323    526.012549     0.285035     0.285035       12.784213   \n",
       "min         1.000000      0.000000     0.000000     0.000000        1.000000   \n",
       "25%       187.000000    181.495000     0.000000     1.000000        1.000000   \n",
       "50%       485.000000    285.334286     0.000000     1.000000        3.000000   \n",
       "75%      1353.000000    422.700000     0.000000     1.000000        7.000000   \n",
       "max    359875.000000  14844.766667     1.000000     1.000000      389.000000   \n",
       "\n",
       "       ...     duration    monthly_f     monthly_m      gap_min      gap_max  \\\n",
       "count  ...  5853.000000  5853.000000   5853.000000  4036.000000  4036.000000   \n",
       "mean   ...     8.760465     1.229117    495.254898     2.822349     5.960605   \n",
       "std    ...     8.343552     0.613260   1093.116996     3.484632     3.903696   \n",
       "min    ...     0.000000     1.000000      0.000000     1.000000     1.000000   \n",
       "25%    ...     0.000000     1.000000    201.396000     1.000000     3.000000   \n",
       "50%    ...     7.000000     1.000000    324.800000     1.000000     5.000000   \n",
       "75%    ...    16.000000     1.285714    508.800000     3.000000     8.000000   \n",
       "max    ...    23.000000    19.375000  39916.500000    23.000000    23.000000   \n",
       "\n",
       "          gap_mean   gap_median      recency  monthly_f_2        churn  \n",
       "count  4036.000000  4036.000000  5853.000000  5853.000000  5853.000000  \n",
       "mean      4.044379     3.760530     7.081668     0.789845     0.439433  \n",
       "std       3.369243     3.483002     6.756999     0.679437     0.496360  \n",
       "min       1.000000     1.000000     1.000000     0.083333     0.000000  \n",
       "25%       1.818182     1.000000     1.000000     0.375000     0.000000  \n",
       "50%       3.000000     2.500000     4.000000     0.714286     0.000000  \n",
       "75%       5.000000     5.000000    13.000000     1.000000     1.000000  \n",
       "max      23.000000    23.000000    24.000000    16.208333     1.000000  \n",
       "\n",
       "[8 rows x 23 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_list.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e43a34a",
   "metadata": {},
   "source": [
    "# missing value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9645d75a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "      <th>churn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1817.0</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "      <td>1817.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.118877</td>\n",
       "      <td>54.081587</td>\n",
       "      <td>430.622582</td>\n",
       "      <td>22.522840</td>\n",
       "      <td>19.900798</td>\n",
       "      <td>288.317556</td>\n",
       "      <td>362.348740</td>\n",
       "      <td>0.090809</td>\n",
       "      <td>0.909191</td>\n",
       "      <td>1.118877</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.118877</td>\n",
       "      <td>430.622582</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.660980</td>\n",
       "      <td>1.118877</td>\n",
       "      <td>0.710512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.441714</td>\n",
       "      <td>452.759494</td>\n",
       "      <td>1251.125685</td>\n",
       "      <td>23.646573</td>\n",
       "      <td>316.733556</td>\n",
       "      <td>2132.169655</td>\n",
       "      <td>683.378959</td>\n",
       "      <td>0.287416</td>\n",
       "      <td>0.287416</td>\n",
       "      <td>0.441714</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.441714</td>\n",
       "      <td>1251.125685</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.448988</td>\n",
       "      <td>0.441714</td>\n",
       "      <td>0.453650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>10.711622</td>\n",
       "      <td>144.150000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.057143</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>136.800000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>144.150000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>16.462500</td>\n",
       "      <td>245.480000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>2.930000</td>\n",
       "      <td>140.000000</td>\n",
       "      <td>233.350000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>245.480000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>22.950000</td>\n",
       "      <td>416.350000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>4.125000</td>\n",
       "      <td>261.000000</td>\n",
       "      <td>382.790000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>416.350000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>8.000000</td>\n",
       "      <td>13305.500000</td>\n",
       "      <td>39916.500000</td>\n",
       "      <td>260.000000</td>\n",
       "      <td>10953.500000</td>\n",
       "      <td>87167.000000</td>\n",
       "      <td>13305.500000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>39916.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         frequency           APV      monetary  product_wide    price_mean  \\\n",
       "count  1817.000000   1817.000000   1817.000000   1817.000000   1817.000000   \n",
       "mean      1.118877     54.081587    430.622582     22.522840     19.900798   \n",
       "std       0.441714    452.759494   1251.125685     23.646573    316.733556   \n",
       "min       1.000000      0.000000      0.000000      1.000000      0.000000   \n",
       "25%       1.000000     10.711622    144.150000      8.000000      2.057143   \n",
       "50%       1.000000     16.462500    245.480000     16.000000      2.930000   \n",
       "75%       1.000000     22.950000    416.350000     29.000000      4.125000   \n",
       "max       8.000000  13305.500000  39916.500000    260.000000  10953.500000   \n",
       "\n",
       "       quantity_sum           ATV     c_Others         c_UK  closed_invoice  \\\n",
       "count   1817.000000   1817.000000  1817.000000  1817.000000     1817.000000   \n",
       "mean     288.317556    362.348740     0.090809     0.909191        1.118877   \n",
       "std     2132.169655    683.378959     0.287416     0.287416        0.441714   \n",
       "min        1.000000      0.000000     0.000000     0.000000        1.000000   \n",
       "25%       70.000000    136.800000     0.000000     1.000000        1.000000   \n",
       "50%      140.000000    233.350000     0.000000     1.000000        1.000000   \n",
       "75%      261.000000    382.790000     0.000000     1.000000        1.000000   \n",
       "max    87167.000000  13305.500000     1.000000     1.000000        8.000000   \n",
       "\n",
       "       ...  duration    monthly_f     monthly_m  gap_min  gap_max  gap_mean  \\\n",
       "count  ...    1817.0  1817.000000   1817.000000      0.0      0.0       0.0   \n",
       "mean   ...       0.0     1.118877    430.622582      NaN      NaN       NaN   \n",
       "std    ...       0.0     0.441714   1251.125685      NaN      NaN       NaN   \n",
       "min    ...       0.0     1.000000      0.000000      NaN      NaN       NaN   \n",
       "25%    ...       0.0     1.000000    144.150000      NaN      NaN       NaN   \n",
       "50%    ...       0.0     1.000000    245.480000      NaN      NaN       NaN   \n",
       "75%    ...       0.0     1.000000    416.350000      NaN      NaN       NaN   \n",
       "max    ...       0.0     8.000000  39916.500000      NaN      NaN       NaN   \n",
       "\n",
       "       gap_median      recency  monthly_f_2        churn  \n",
       "count         0.0  1817.000000  1817.000000  1817.000000  \n",
       "mean          NaN    11.660980     1.118877     0.710512  \n",
       "std           NaN     7.448988     0.441714     0.453650  \n",
       "min           NaN     1.000000     1.000000     0.000000  \n",
       "25%           NaN     4.000000     1.000000     0.000000  \n",
       "50%           NaN    13.000000     1.000000     1.000000  \n",
       "75%           NaN    18.000000     1.000000     1.000000  \n",
       "max           NaN    24.000000     8.000000     1.000000  \n",
       "\n",
       "[8 rows x 23 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_list[feature_list[\"gap_min\"].isna()].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc98aeac",
   "metadata": {},
   "source": [
    "## case 1: all valid transaction all happened in one month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "c52cbcf5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "      <th>churn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17509.0</th>\n",
       "      <td>8</td>\n",
       "      <td>16.708033</td>\n",
       "      <td>6115.14</td>\n",
       "      <td>75</td>\n",
       "      <td>2.259208</td>\n",
       "      <td>3534</td>\n",
       "      <td>764.3925</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6115.14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             frequency        APV  monetary  product_wide  price_mean  \\\n",
       "Customer ID                                                             \n",
       "17509.0              8  16.708033   6115.14            75    2.259208   \n",
       "\n",
       "             quantity_sum       ATV  c_Others  c_UK  closed_invoice  ...  \\\n",
       "Customer ID                                                          ...   \n",
       "17509.0              3534  764.3925         0     1               8  ...   \n",
       "\n",
       "             duration  monthly_f  monthly_m  gap_min  gap_max  gap_mean  \\\n",
       "Customer ID                                                               \n",
       "17509.0           0.0        8.0    6115.14      NaN      NaN       NaN   \n",
       "\n",
       "             gap_median  recency  monthly_f_2  churn  \n",
       "Customer ID                                           \n",
       "17509.0             NaN      2.0          8.0      0  \n",
       "\n",
       "[1 rows x 23 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_list[(feature_list[\"gap_min\"].isna())&(feature_list[\"frequency\"]==8)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "2602bcb0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "      <th>churn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12365.0</th>\n",
       "      <td>2</td>\n",
       "      <td>29.153636</td>\n",
       "      <td>641.38</td>\n",
       "      <td>22</td>\n",
       "      <td>17.150455</td>\n",
       "      <td>174</td>\n",
       "      <td>320.690</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>641.38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12452.0</th>\n",
       "      <td>2</td>\n",
       "      <td>19.571364</td>\n",
       "      <td>430.57</td>\n",
       "      <td>21</td>\n",
       "      <td>4.485000</td>\n",
       "      <td>185</td>\n",
       "      <td>215.285</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>430.57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12496.0</th>\n",
       "      <td>2</td>\n",
       "      <td>4.160769</td>\n",
       "      <td>54.09</td>\n",
       "      <td>10</td>\n",
       "      <td>2.155385</td>\n",
       "      <td>33</td>\n",
       "      <td>27.045</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>54.09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12508.0</th>\n",
       "      <td>2</td>\n",
       "      <td>5.772029</td>\n",
       "      <td>398.27</td>\n",
       "      <td>57</td>\n",
       "      <td>1.720435</td>\n",
       "      <td>272</td>\n",
       "      <td>199.135</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>398.27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12512.0</th>\n",
       "      <td>2</td>\n",
       "      <td>4.059706</td>\n",
       "      <td>138.03</td>\n",
       "      <td>31</td>\n",
       "      <td>2.579412</td>\n",
       "      <td>99</td>\n",
       "      <td>69.015</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>138.03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18047.0</th>\n",
       "      <td>2</td>\n",
       "      <td>3.894205</td>\n",
       "      <td>342.69</td>\n",
       "      <td>74</td>\n",
       "      <td>2.800455</td>\n",
       "      <td>138</td>\n",
       "      <td>171.345</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>342.69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18121.0</th>\n",
       "      <td>2</td>\n",
       "      <td>21.034375</td>\n",
       "      <td>673.10</td>\n",
       "      <td>28</td>\n",
       "      <td>1.865000</td>\n",
       "      <td>450</td>\n",
       "      <td>336.550</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>673.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18137.0</th>\n",
       "      <td>2</td>\n",
       "      <td>5.034138</td>\n",
       "      <td>145.99</td>\n",
       "      <td>26</td>\n",
       "      <td>3.514483</td>\n",
       "      <td>59</td>\n",
       "      <td>72.995</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>145.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18139.0</th>\n",
       "      <td>6</td>\n",
       "      <td>53.071321</td>\n",
       "      <td>8438.34</td>\n",
       "      <td>35</td>\n",
       "      <td>1.509245</td>\n",
       "      <td>5557</td>\n",
       "      <td>1406.390</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8438.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18214.0</th>\n",
       "      <td>4</td>\n",
       "      <td>30.695517</td>\n",
       "      <td>1780.34</td>\n",
       "      <td>38</td>\n",
       "      <td>3.123276</td>\n",
       "      <td>989</td>\n",
       "      <td>445.085</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1780.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>164 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             frequency        APV  monetary  product_wide  price_mean  \\\n",
       "Customer ID                                                             \n",
       "12365.0              2  29.153636    641.38            22   17.150455   \n",
       "12452.0              2  19.571364    430.57            21    4.485000   \n",
       "12496.0              2   4.160769     54.09            10    2.155385   \n",
       "12508.0              2   5.772029    398.27            57    1.720435   \n",
       "12512.0              2   4.059706    138.03            31    2.579412   \n",
       "...                ...        ...       ...           ...         ...   \n",
       "18047.0              2   3.894205    342.69            74    2.800455   \n",
       "18121.0              2  21.034375    673.10            28    1.865000   \n",
       "18137.0              2   5.034138    145.99            26    3.514483   \n",
       "18139.0              6  53.071321   8438.34            35    1.509245   \n",
       "18214.0              4  30.695517   1780.34            38    3.123276   \n",
       "\n",
       "             quantity_sum       ATV  c_Others  c_UK  closed_invoice  ...  \\\n",
       "Customer ID                                                          ...   \n",
       "12365.0               174   320.690         1     0               2  ...   \n",
       "12452.0               185   215.285         1     0               2  ...   \n",
       "12496.0                33    27.045         1     0               2  ...   \n",
       "12508.0               272   199.135         1     0               2  ...   \n",
       "12512.0                99    69.015         1     0               2  ...   \n",
       "...                   ...       ...       ...   ...             ...  ...   \n",
       "18047.0               138   171.345         0     1               2  ...   \n",
       "18121.0               450   336.550         0     1               2  ...   \n",
       "18137.0                59    72.995         0     1               2  ...   \n",
       "18139.0              5557  1406.390         0     1               6  ...   \n",
       "18214.0               989   445.085         0     1               4  ...   \n",
       "\n",
       "             duration  monthly_f  monthly_m  gap_min  gap_max  gap_mean  \\\n",
       "Customer ID                                                               \n",
       "12365.0           0.0        2.0     641.38      NaN      NaN       NaN   \n",
       "12452.0           0.0        2.0     430.57      NaN      NaN       NaN   \n",
       "12496.0           0.0        2.0      54.09      NaN      NaN       NaN   \n",
       "12508.0           0.0        2.0     398.27      NaN      NaN       NaN   \n",
       "12512.0           0.0        2.0     138.03      NaN      NaN       NaN   \n",
       "...               ...        ...        ...      ...      ...       ...   \n",
       "18047.0           0.0        2.0     342.69      NaN      NaN       NaN   \n",
       "18121.0           0.0        2.0     673.10      NaN      NaN       NaN   \n",
       "18137.0           0.0        2.0     145.99      NaN      NaN       NaN   \n",
       "18139.0           0.0        6.0    8438.34      NaN      NaN       NaN   \n",
       "18214.0           0.0        4.0    1780.34      NaN      NaN       NaN   \n",
       "\n",
       "             gap_median  recency  monthly_f_2  churn  \n",
       "Customer ID                                           \n",
       "12365.0             NaN     10.0          2.0      1  \n",
       "12452.0             NaN      1.0          2.0      0  \n",
       "12496.0             NaN     21.0          2.0      1  \n",
       "12508.0             NaN      1.0          2.0      0  \n",
       "12512.0             NaN      2.0          2.0      0  \n",
       "...                 ...      ...          ...    ...  \n",
       "18047.0             NaN     13.0          2.0      1  \n",
       "18121.0             NaN      5.0          2.0      0  \n",
       "18137.0             NaN     18.0          2.0      1  \n",
       "18139.0             NaN      1.0          6.0      0  \n",
       "18214.0             NaN     18.0          4.0      1  \n",
       "\n",
       "[164 rows x 23 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 如何找到这群人？\n",
    "# 首先是有观察记录中的交易平均值Monthly_F等于全部交易值Total_invoice\n",
    "# 其次是有观察记录中的交易平均值Total_invoice大于1\n",
    "feature_list[(feature_list[\"closed_invoice\"]==feature_list[\"monthly_f\"]) \\\n",
    "             & (feature_list[\"closed_invoice\"]>1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "4caab6c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# in this case, we set gap_4 to 0\n",
    "feature_list_v1 = feature_list.copy()\n",
    "feature_list_v1.loc[(feature_list_v1[\"closed_invoice\"]==feature_list_v1[\"monthly_f\"]) \\\n",
    "             & (feature_list_v1[\"closed_invoice\"]>1), \n",
    "             ['gap_min','gap_max','gap_mean','gap_median']] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "32469428",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "      <th>churn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1653.0</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1653.0</td>\n",
       "      <td>1653.0</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1653.000000</td>\n",
       "      <td>1653.0</td>\n",
       "      <td>1653.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.0</td>\n",
       "      <td>40.545783</td>\n",
       "      <td>348.634538</td>\n",
       "      <td>21.112523</td>\n",
       "      <td>13.672182</td>\n",
       "      <td>266.098004</td>\n",
       "      <td>348.634538</td>\n",
       "      <td>0.088929</td>\n",
       "      <td>0.911071</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>348.634538</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.809437</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.719903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>290.266529</td>\n",
       "      <td>591.082019</td>\n",
       "      <td>21.684691</td>\n",
       "      <td>275.663618</td>\n",
       "      <td>2215.933223</td>\n",
       "      <td>591.082019</td>\n",
       "      <td>0.284728</td>\n",
       "      <td>0.284728</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>591.082019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.432545</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.449182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.135000</td>\n",
       "      <td>136.800000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.050000</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>136.800000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>136.800000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>16.443953</td>\n",
       "      <td>233.350000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>2.941400</td>\n",
       "      <td>132.000000</td>\n",
       "      <td>233.350000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>233.350000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>22.267857</td>\n",
       "      <td>381.660000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>4.161053</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>381.660000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>381.660000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10953.500000</td>\n",
       "      <td>11880.840000</td>\n",
       "      <td>220.000000</td>\n",
       "      <td>10953.500000</td>\n",
       "      <td>87167.000000</td>\n",
       "      <td>11880.840000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11880.840000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       frequency           APV      monetary  product_wide    price_mean  \\\n",
       "count     1653.0   1653.000000   1653.000000   1653.000000   1653.000000   \n",
       "mean         1.0     40.545783    348.634538     21.112523     13.672182   \n",
       "std          0.0    290.266529    591.082019     21.684691    275.663618   \n",
       "min          1.0      0.000000      0.000000      1.000000      0.000000   \n",
       "25%          1.0     11.135000    136.800000      8.000000      2.050000   \n",
       "50%          1.0     16.443953    233.350000     16.000000      2.941400   \n",
       "75%          1.0     22.267857    381.660000     27.000000      4.161053   \n",
       "max          1.0  10953.500000  11880.840000    220.000000  10953.500000   \n",
       "\n",
       "       quantity_sum           ATV     c_Others         c_UK  closed_invoice  \\\n",
       "count   1653.000000   1653.000000  1653.000000  1653.000000          1653.0   \n",
       "mean     266.098004    348.634538     0.088929     0.911071             1.0   \n",
       "std     2215.933223    591.082019     0.284728     0.284728             0.0   \n",
       "min        1.000000      0.000000     0.000000     0.000000             1.0   \n",
       "25%       66.000000    136.800000     0.000000     1.000000             1.0   \n",
       "50%      132.000000    233.350000     0.000000     1.000000             1.0   \n",
       "75%      242.000000    381.660000     0.000000     1.000000             1.0   \n",
       "max    87167.000000  11880.840000     1.000000     1.000000             1.0   \n",
       "\n",
       "       ...  duration  monthly_f     monthly_m  gap_min  gap_max  gap_mean  \\\n",
       "count  ...    1653.0     1653.0   1653.000000      0.0      0.0       0.0   \n",
       "mean   ...       0.0        1.0    348.634538      NaN      NaN       NaN   \n",
       "std    ...       0.0        0.0    591.082019      NaN      NaN       NaN   \n",
       "min    ...       0.0        1.0      0.000000      NaN      NaN       NaN   \n",
       "25%    ...       0.0        1.0    136.800000      NaN      NaN       NaN   \n",
       "50%    ...       0.0        1.0    233.350000      NaN      NaN       NaN   \n",
       "75%    ...       0.0        1.0    381.660000      NaN      NaN       NaN   \n",
       "max    ...       0.0        1.0  11880.840000      NaN      NaN       NaN   \n",
       "\n",
       "       gap_median      recency  monthly_f_2        churn  \n",
       "count         0.0  1653.000000       1653.0  1653.000000  \n",
       "mean          NaN    11.809437          1.0     0.719903  \n",
       "std           NaN     7.432545          0.0     0.449182  \n",
       "min           NaN     1.000000          1.0     0.000000  \n",
       "25%           NaN     4.000000          1.0     0.000000  \n",
       "50%           NaN    13.000000          1.0     1.000000  \n",
       "75%           NaN    18.000000          1.0     1.000000  \n",
       "max           NaN    24.000000          1.0     1.000000  \n",
       "\n",
       "[8 rows x 23 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_list_v1[(feature_list_v1['gap_min'].isna())].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3de74bdc",
   "metadata": {},
   "source": [
    "## case 2: only one transaction happened"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "81d96f68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>cancel_rate</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>churn</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.210433</td>\n",
       "      <td>28.930213</td>\n",
       "      <td>5116.853273</td>\n",
       "      <td>126.494322</td>\n",
       "      <td>3.334574</td>\n",
       "      <td>3023.678495</td>\n",
       "      <td>398.790145</td>\n",
       "      <td>0.095103</td>\n",
       "      <td>0.904897</td>\n",
       "      <td>10.210433</td>\n",
       "      <td>...</td>\n",
       "      <td>0.134903</td>\n",
       "      <td>14.644429</td>\n",
       "      <td>1.337661</td>\n",
       "      <td>574.619732</td>\n",
       "      <td>2.556778</td>\n",
       "      <td>6.188786</td>\n",
       "      <td>3.933161</td>\n",
       "      <td>3.594925</td>\n",
       "      <td>1.899929</td>\n",
       "      <td>0.706048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.175832</td>\n",
       "      <td>53.014618</td>\n",
       "      <td>1613.337896</td>\n",
       "      <td>59.916787</td>\n",
       "      <td>13.299119</td>\n",
       "      <td>1065.789436</td>\n",
       "      <td>367.020034</td>\n",
       "      <td>0.077424</td>\n",
       "      <td>0.922576</td>\n",
       "      <td>4.175832</td>\n",
       "      <td>...</td>\n",
       "      <td>0.135907</td>\n",
       "      <td>7.240955</td>\n",
       "      <td>1.281835</td>\n",
       "      <td>508.795676</td>\n",
       "      <td>3.028944</td>\n",
       "      <td>4.787988</td>\n",
       "      <td>3.791221</td>\n",
       "      <td>3.651954</td>\n",
       "      <td>11.992764</td>\n",
       "      <td>0.709349</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       frequency        APV     monetary  product_wide  price_mean  \\\n",
       "churn                                                                \n",
       "0      10.210433  28.930213  5116.853273    126.494322    3.334574   \n",
       "1       4.175832  53.014618  1613.337896     59.916787   13.299119   \n",
       "\n",
       "       quantity_sum         ATV  c_Others      c_UK  closed_invoice  ...  \\\n",
       "churn                                                                ...   \n",
       "0       3023.678495  398.790145  0.095103  0.904897       10.210433  ...   \n",
       "1       1065.789436  367.020034  0.077424  0.922576        4.175832  ...   \n",
       "\n",
       "       cancel_rate   duration  monthly_f   monthly_m   gap_min   gap_max  \\\n",
       "churn                                                                      \n",
       "0         0.134903  14.644429   1.337661  574.619732  2.556778  6.188786   \n",
       "1         0.135907   7.240955   1.281835  508.795676  3.028944  4.787988   \n",
       "\n",
       "       gap_mean  gap_median    recency  monthly_f_2  \n",
       "churn                                                \n",
       "0      3.933161    3.594925   1.899929     0.706048  \n",
       "1      3.791221    3.651954  11.992764     0.709349  \n",
       "\n",
       "[2 rows x 22 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 由于只消费一次的用户一定是流失用户\n",
    "# 我们使用流失用户的mean对gap_4进行替代，\n",
    "# 这样能够最小程度上的影响流失用户的分布\n",
    "feature_list_v1[~(feature_list_v1['gap_min'].isna())].groupby('churn').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "577b54b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "churn_mean = feature_list_v1[feature_list_v1['churn']==1] \\\n",
    "                [['gap_min','gap_max','gap_mean','gap_median']].mean().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4215f04c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frequency</th>\n",
       "      <th>APV</th>\n",
       "      <th>monetary</th>\n",
       "      <th>product_wide</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>quantity_sum</th>\n",
       "      <th>ATV</th>\n",
       "      <th>c_Others</th>\n",
       "      <th>c_UK</th>\n",
       "      <th>closed_invoice</th>\n",
       "      <th>...</th>\n",
       "      <th>duration</th>\n",
       "      <th>monthly_f</th>\n",
       "      <th>monthly_m</th>\n",
       "      <th>gap_min</th>\n",
       "      <th>gap_max</th>\n",
       "      <th>gap_mean</th>\n",
       "      <th>gap_median</th>\n",
       "      <th>recency</th>\n",
       "      <th>monthly_f_2</th>\n",
       "      <th>churn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "      <td>5390.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>6.629685</td>\n",
       "      <td>38.510093</td>\n",
       "      <td>3162.770443</td>\n",
       "      <td>85.859369</td>\n",
       "      <td>8.617887</td>\n",
       "      <td>1912.446011</td>\n",
       "      <td>376.515679</td>\n",
       "      <td>0.087384</td>\n",
       "      <td>0.912616</td>\n",
       "      <td>6.629685</td>\n",
       "      <td>...</td>\n",
       "      <td>9.512987</td>\n",
       "      <td>1.248798</td>\n",
       "      <td>504.794289</td>\n",
       "      <td>2.782086</td>\n",
       "      <td>5.520354</td>\n",
       "      <td>3.865430</td>\n",
       "      <td>3.622138</td>\n",
       "      <td>7.496846</td>\n",
       "      <td>0.771793</td>\n",
       "      <td>0.477180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>13.227633</td>\n",
       "      <td>274.785031</td>\n",
       "      <td>14920.967122</td>\n",
       "      <td>118.491550</td>\n",
       "      <td>181.940900</td>\n",
       "      <td>9147.225198</td>\n",
       "      <td>532.565160</td>\n",
       "      <td>0.282423</td>\n",
       "      <td>0.282423</td>\n",
       "      <td>13.227633</td>\n",
       "      <td>...</td>\n",
       "      <td>8.272594</td>\n",
       "      <td>0.635218</td>\n",
       "      <td>1131.187202</td>\n",
       "      <td>3.056469</td>\n",
       "      <td>3.549796</td>\n",
       "      <td>2.996587</td>\n",
       "      <td>3.081735</td>\n",
       "      <td>6.875607</td>\n",
       "      <td>0.705107</td>\n",
       "      <td>0.499525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.083333</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>11.418642</td>\n",
       "      <td>389.052500</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>2.322093</td>\n",
       "      <td>210.000000</td>\n",
       "      <td>183.782054</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>205.998750</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.350000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>17.409854</td>\n",
       "      <td>997.120000</td>\n",
       "      <td>48.000000</td>\n",
       "      <td>2.982915</td>\n",
       "      <td>538.000000</td>\n",
       "      <td>285.831447</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>329.270000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>4.787988</td>\n",
       "      <td>3.791221</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0.652174</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>24.354574</td>\n",
       "      <td>2482.755000</td>\n",
       "      <td>109.000000</td>\n",
       "      <td>3.872179</td>\n",
       "      <td>1450.000000</td>\n",
       "      <td>421.376250</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>514.882500</td>\n",
       "      <td>3.028944</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>389.000000</td>\n",
       "      <td>13305.500000</td>\n",
       "      <td>597336.110000</td>\n",
       "      <td>2515.000000</td>\n",
       "      <td>10953.500000</td>\n",
       "      <td>359875.000000</td>\n",
       "      <td>14844.766667</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>389.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>19.375000</td>\n",
       "      <td>39916.500000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>16.208333</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         frequency           APV       monetary  product_wide    price_mean  \\\n",
       "count  5390.000000   5390.000000    5390.000000   5390.000000   5390.000000   \n",
       "mean      6.629685     38.510093    3162.770443     85.859369      8.617887   \n",
       "std      13.227633    274.785031   14920.967122    118.491550    181.940900   \n",
       "min       1.000000      0.000000       0.000000      1.000000      0.000000   \n",
       "25%       2.000000     11.418642     389.052500     21.000000      2.322093   \n",
       "50%       3.000000     17.409854     997.120000     48.000000      2.982915   \n",
       "75%       7.000000     24.354574    2482.755000    109.000000      3.872179   \n",
       "max     389.000000  13305.500000  597336.110000   2515.000000  10953.500000   \n",
       "\n",
       "        quantity_sum           ATV     c_Others         c_UK  closed_invoice  \\\n",
       "count    5390.000000   5390.000000  5390.000000  5390.000000     5390.000000   \n",
       "mean     1912.446011    376.515679     0.087384     0.912616        6.629685   \n",
       "std      9147.225198    532.565160     0.282423     0.282423       13.227633   \n",
       "min         1.000000      0.000000     0.000000     0.000000        1.000000   \n",
       "25%       210.000000    183.782054     0.000000     1.000000        2.000000   \n",
       "50%       538.000000    285.831447     0.000000     1.000000        3.000000   \n",
       "75%      1450.000000    421.376250     0.000000     1.000000        7.000000   \n",
       "max    359875.000000  14844.766667     1.000000     1.000000      389.000000   \n",
       "\n",
       "       ...     duration    monthly_f     monthly_m      gap_min      gap_max  \\\n",
       "count  ...  5390.000000  5390.000000   5390.000000  5390.000000  5390.000000   \n",
       "mean   ...     9.512987     1.248798    504.794289     2.782086     5.520354   \n",
       "std    ...     8.272594     0.635218   1131.187202     3.056469     3.549796   \n",
       "min    ...     0.000000     1.000000      0.000000     0.000000     0.000000   \n",
       "25%    ...     0.000000     1.000000    205.998750     1.000000     3.000000   \n",
       "50%    ...     9.000000     1.000000    329.270000     2.000000     4.787988   \n",
       "75%    ...    17.000000     1.333333    514.882500     3.028944     7.000000   \n",
       "max    ...    23.000000    19.375000  39916.500000    23.000000    23.000000   \n",
       "\n",
       "          gap_mean   gap_median      recency  monthly_f_2        churn  \n",
       "count  5390.000000  5390.000000  5390.000000  5390.000000  5390.000000  \n",
       "mean      3.865430     3.622138     7.496846     0.771793     0.477180  \n",
       "std       2.996587     3.081735     6.875607     0.705107     0.499525  \n",
       "min       0.000000     0.000000     1.000000     0.083333     0.000000  \n",
       "25%       2.000000     2.000000     1.000000     0.350000     0.000000  \n",
       "50%       3.791221     3.000000     5.000000     0.652174     0.000000  \n",
       "75%       4.000000     4.000000    13.000000     1.000000     1.000000  \n",
       "max      23.000000    23.000000    24.000000    16.208333     1.000000  \n",
       "\n",
       "[8 rows x 23 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_list_v1.loc[(feature_list_v1[\"gap_min\"].isna())&\n",
    "                    (feature_list_v1['churn']==1),\n",
    "                    ['gap_min','gap_max','gap_mean','gap_median']] \\\n",
    "                    = churn_mean\n",
    "feature_list_v2 = feature_list_v1.dropna()\n",
    "feature_list_v2.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e6ee4e6",
   "metadata": {},
   "source": [
    "## case 3: the rest of customer \n",
    "这些用户不是流失分析的主要研究对象，因此我们选择删除"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89955e73",
   "metadata": {},
   "source": [
    "# EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "4d9b9fc0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.52282\n",
       "1    0.47718\n",
       "Name: churn, dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# after dropping values:\n",
    "feature_list_v2[\"churn\"].value_counts()/feature_list_v2[\"churn\"].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "9fc6b5fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.560567\n",
       "1    0.439433\n",
       "Name: churn, dtype: float64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# before dropping values:\n",
    "feature_list_v1[\"churn\"].value_counts()/feature_list_v1[\"churn\"].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6041a3d9",
   "metadata": {},
   "source": [
    "# store feature matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b51f1192",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_list_v2.to_csv(\"feature.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "270.195px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
